﻿CREATE PROCEDURE [dbo].[proc_CallCenter_GetList_Department]
	(
		@CompanyId int,
		@StartDate varchar(10),
		@EndDate varchar(10)
	)
As
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

--;WITH List As (
--	Select ROW_NUMBER() OVER (ORDER BY bmbh)AS Row,
--	Id,
--	ParentId,
--	(Case When len(bmbh)>4 Then Replicate('┆┄┄',(Len(bmbh)-4)/2)+IsNull(dname,'') Else '◆'+IsNull(dname,'') End) As DName,
--	Sort,
--	CompanyId,
--	bmbh
----	(Select Count(0) From CallCenter_Record WHERE CompanyId=@CompanyId and DepartmentId=d.Id And [Type]='CH_CALLOUT' And EndDate>=@sRq And EndDate<=@eRq) As OutCount,
----	(Select Count(0) From CallCenter_Record Where CompanyId=@CompanyId and DepartmentId=d.Id And [Type]='CH_PHONEIN' And EndDate>=@sRq And EndDate<=@eRq) As InCount
--	From Department
--	Where CompanyId=@CompanyId 
--)
--
--Select *,
----	0 As OutCount,
--	(Select Count(0) From CallCenter_Record WHERE EndDate>=@sRq And EndDate<=@eRq And CompanyId=@CompanyId And DepartmentId=List.Id And [Type]='CH_CALLOUT') As OutCount,
--	(Select Count(0) From CallCenter_Record Where EndDate>=@sRq And EndDate<=@eRq And CompanyId=@CompanyId And DepartmentId=List.Id And [Type]='CH_PHONEIN') As InCount
----	0 As InCount
--From List
--Order By Row

	-- By ZHT 2014-11-17 使用CallCenter_Record_Sum表减少统计消耗

	;WITH List As(
		Select DeptId,Sum(Call_In) As Call_In,Sum(Call_Out) As Call_Out
		From CallCenter_Record_Sum
		Where CompanyId=@CompanyId And CreateDate>=@sRq And CreateDate<=@eRq
		Group By DeptId
	)
	Select a.Id,
	(Case When len(a.bmbh)>4 Then Replicate('┆┄┄',(Len(a.bmbh)-4)/2)+IsNull(a.dname,'') Else '◆'+IsNull(a.dname,'') End) As DName,
	a.Sort,a.CompanyId,a.bmbh,
	IsNull(b.Call_In,0) As InCount,IsNull(b.Call_Out,0) As OutCount
	From Department a LEFT JOIN List b On a.Id = b.DeptId
	Where a.CompanyId=@CompanyId
	Union All
	Select -1,'未分配号码的分机',0,@CompanyId,'9999999999',
	IsNull(Call_In,0),IsNull(Call_Out,0)
	From List Where DeptId=-1
	Order by a.bmbh
End
